import prisma from '../lib/prisma';
import { logger } from '../utils/logger';

export class TeachingAnalysisService {
  // 获取教师教学评估数据
  static async getTeacherEvaluation(teacherId: string) {
    try {
      // 获取教师的所有批改记录
      const assessments = await prisma.submission.findMany({
        where: {
          question: {
            creatorId: teacherId
          }
        },
        include: {
          question: true,
          user: {
            select: {
              id: true,
              name: true
            }
          }
        }
      });

      // 计算平均批改时间
      const avgGradingTime = await prisma.$queryRaw`
        SELECT AVG(EXTRACT(EPOCH FROM (updated_at - created_at)))::float
        FROM "Submission"
        WHERE "questionId" IN (
          SELECT id FROM "Question" WHERE "creatorId" = ${teacherId}
        )
      `;

      // 获取题目难度分布
      const difficultyDistribution = await prisma.question.groupBy({
        by: ['difficulty'],
        where: {
          creatorId: teacherId
        },
        _count: true
      });

      // 获取学生成绩分布
      const scoreDistribution = await prisma.$queryRaw`
        SELECT
          CASE
            WHEN score >= 90 THEN '优秀'
            WHEN score >= 80 THEN '良好'
            WHEN score >= 70 THEN '中等'
            WHEN score >= 60 THEN '及格'
            ELSE '不及格'
          END as range,
          COUNT(*)::int as count
        FROM "Submission"
        WHERE "questionId" IN (
          SELECT id FROM "Question" WHERE "creatorId" = ${teacherId}
        )
        GROUP BY range
        ORDER BY MIN(score) DESC
      `;

      // 获取知识点掌握情况
      const knowledgePoints = await prisma.$queryRaw`
        WITH knowledge_stats AS (
          SELECT
            q."knowledgePoint",
            AVG(s.score) as avg_score,
            COUNT(*) as attempt_count
          FROM "Question" q
          JOIN "Submission" s ON s."questionId" = q.id
          WHERE q."creatorId" = ${teacherId}
          GROUP BY q."knowledgePoint"
        )
        SELECT
          "knowledgePoint",
          avg_score::float as average_score,
          attempt_count::int,
          CASE
            WHEN avg_score >= 85 THEN '优秀'
            WHEN avg_score >= 70 THEN '良好'
            WHEN avg_score >= 60 THEN '中等'
            ELSE '需加强'
          END as mastery_level
        FROM knowledge_stats
        ORDER BY avg_score DESC
      `;

      // 获取教学效果趋势
      const teachingTrend = await prisma.$queryRaw`
        SELECT
          DATE_TRUNC('week', s.created_at) as week,
          AVG(s.score)::float as avg_score,
          COUNT(DISTINCT s."userId")::int as student_count
        FROM "Submission" s
        JOIN "Question" q ON s."questionId" = q.id
        WHERE q."creatorId" = ${teacherId}
        GROUP BY week
        ORDER BY week DESC
        LIMIT 10
      `;

      // 获取学生个体进步情况
      const studentProgress = await prisma.$queryRaw`
        WITH student_submissions AS (
          SELECT
            s."userId",
            u.name as student_name,
            s.created_at,
            s.score,
            ROW_NUMBER() OVER (PARTITION BY s."userId" ORDER BY s.created_at) as submission_order
          FROM "Submission" s
          JOIN "User" u ON u.id = s."userId"
          JOIN "Question" q ON q.id = s."questionId"
          WHERE q."creatorId" = ${teacherId}
        )
        SELECT
          "userId",
          student_name,
          AVG(CASE WHEN submission_order <= 3 THEN score END)::float as initial_avg_score,
          AVG(CASE WHEN submission_order > 3 THEN score END)::float as recent_avg_score,
          COUNT(*)::int as total_submissions
        FROM student_submissions
        GROUP BY "userId", student_name
        HAVING COUNT(*) >= 6
        ORDER BY (AVG(CASE WHEN submission_order > 3 THEN score END) -
                 AVG(CASE WHEN submission_order <= 3 THEN score END)) DESC
      `;

      return {
        assessments,
        avgGradingTime,
        difficultyDistribution,
        scoreDistribution,
        knowledgePoints,
        teachingTrend,
        studentProgress
      };
    } catch (error) {
      logger.error('Error getting teacher evaluation', { error, teacherId });
      throw error;
    }
  }

  // 获取教学建议
  static async getTeachingRecommendations(teacherId: string) {
    try {
      const recommendations = [];

      // 获取成绩较低的知识点
      const weakKnowledgePoints = await prisma.$queryRaw`
        SELECT
          q."knowledgePoint",
          AVG(s.score)::float as avg_score,
          COUNT(*)::int as attempt_count
        FROM "Question" q
        JOIN "Submission" s ON s."questionId" = q.id
        WHERE q."creatorId" = ${teacherId}
        GROUP BY q."knowledgePoint"
        HAVING AVG(s.score) < 70
        ORDER BY avg_score ASC
      `;

      if (weakKnowledgePoints.length > 0) {
        recommendations.push({
          type: 'knowledge_points',
          title: '知识点加强建议',
          details: weakKnowledgePoints,
          suggestion: '建议加强以下知识点的教学：' +
            weakKnowledgePoints.map((kp: any) => kp.knowledgePoint).join('、')
        });
      }

      // 分析批改时间过长的题型
      const slowGradingTypes = await prisma.$queryRaw`
        SELECT
          q.type,
          AVG(EXTRACT(EPOCH FROM (s.updated_at - s.created_at)))::float as avg_grading_time,
          COUNT(*)::int as submission_count
        FROM "Submission" s
        JOIN "Question" q ON q.id = s."questionId"
        WHERE q."creatorId" = ${teacherId}
        GROUP BY q.type
        HAVING AVG(EXTRACT(EPOCH FROM (s.updated_at - s.created_at))) > 3600
        ORDER BY avg_grading_time DESC
      `;

      if (slowGradingTypes.length > 0) {
        recommendations.push({
          type: 'grading_efficiency',
          title: '批改效率建议',
          details: slowGradingTypes,
          suggestion: '以下题型的批改时间较长，建议：\n' +
            '1. 考虑使用AI辅助批改\n' +
            '2. 优化题目设计，增加客观题比例\n' +
            '3. 建立标准化的评分标准'
        });
      }

      // 分析学生进步缓慢的知识领域
      const slowProgressAreas = await prisma.$queryRaw`
        WITH progress_by_area AS (
          SELECT
            q."subjectArea",
            AVG(CASE
              WHEN s.created_at > (now() - interval '1 month') THEN s.score
              ELSE NULL
            END) as recent_avg,
            AVG(CASE
              WHEN s.created_at <= (now() - interval '1 month') THEN s.score
              ELSE NULL
            END) as previous_avg
          FROM "Question" q
          JOIN "Submission" s ON s."questionId" = q.id
          WHERE q."creatorId" = ${teacherId}
          GROUP BY q."subjectArea"
        )
        SELECT
          "subjectArea",
          recent_avg::float,
          previous_avg::float,
          (recent_avg - previous_avg)::float as improvement
        FROM progress_by_area
        WHERE (recent_avg - previous_avg) < 5
        ORDER BY improvement ASC
      `;

      if (slowProgressAreas.length > 0) {
        recommendations.push({
          type: 'teaching_methods',
          title: '教学方法改进建议',
          details: slowProgressAreas,
          suggestion: '在以下领域学生进步较慢，建议：\n' +
            '1. 调整教学方法和策略\n' +
            '2. 增加练习题的数量和难度梯度\n' +
            '3. 考虑采用更多互动式教学方法'
        });
      }

      return recommendations;
    } catch (error) {
      logger.error('Error getting teaching recommendations', { error, teacherId });
      throw error;
    }
  }
}
